﻿using Model;
using System.Data;

namespace BLL
{
    public class Sys_SignatureBLL : BaseBLL
    {
        public DataTable GetInfoByCon(string Type, object param)
        {
            string sql = string.Format(@"SELECT
    syss.SIGNATUREID,
    syss.SYSUSERID,
    CASE syss.SIGNATURETYPE
    WHEN '1' THEN '私章'
    WHEN '0' THEN '公章'
    END as SIGNATURETYPE,
    syss.SIGNATUREPWD,
    syss.SIGNATUREPATH,
    syss.STATE,
    syss.SIGNATURENAME,
    sysu.USER_NAME
FROM
    THKQS.Sys_Signature syss,
    THKQS.Sys_User sysu
WHERE
    syss.SYSUSERID = sysu.USER_ID");
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');
                if (!string.IsNullOrEmpty(str[0]))
                {
                    sql += " AND syss.SYSUSERID=" + str[0];
                }
                if (!string.IsNullOrEmpty(str[1]))
                {
                    sql += " AND syss.SIGNATURETYPE = " + str[1];
                }
            }
            if (Type == "LogonName")
            {
                sql += " AND sysu.USER_LOGINNAME='" + param.ToString() + "'";
            }
            sql += " ORDER BY syss.SYSUSERID  DESC";
            DataTable dt = Query(sql);
            return dt;
        }

        public int INSERT(Sys_Signature model, string operName)
        {
            model.SIGNATUREID = GetSequence("SYS_SIGNATURE");
            string sql = string.Format(@"INSERT INTO Sys_Signature(
SIGNATUREID,
SYSUSERID,
SIGNATURETYPE,
SIGNATUREPWD,
SIGNATUREPATH,
STATE,
SIGNATURENAME)
VALUES( '{0}', '{1}', '{2}', '{3}', '{4}', '{5}','{6}')
", model.SIGNATUREID, model.SYSUSERID, model.SIGNATURETYPE, model.SIGNATUREPWD, model.SIGNATUREPATH, model.STATE, model.SIGNATURENAME);
            int count = NoQuery(sql, operName);
            return count;
        }

        public int UPDATE(Sys_Signature model, string operName)
        {
            string sql = string.Format(@"UPDATE THKQS.Sys_Signature SET
SIGNATURENAME = '{0}',
SIGNATUREPATH = '{1}',
SIGNATUREPWD  = '{2}'
WHERE SIGNATUREID = '{3}'
", model.SIGNATURENAME, model.SIGNATUREPATH, model.SIGNATUREPWD, model.SIGNATUREID);
            int count = NoQuery(sql, operName);
            return count;
        }

        public int DELETE(string ids, string operName)
        {
            string sql = string.Format(@"DELETE FROM THKQS.Sys_Signature WHERE SIGNATUREID IN(" + ids + ")");
            int count = NoQuery(sql, operName);
            return count;
        }

        public DataTable GetSignture(decimal userId, decimal type)
        {
            string sql = string.Format(@"SELECT SIGNATUREID, SYSUSERID, SIGNATURETYPE,SIGNATUREPWD,SIGNATUREPATH, STATE,SIGNATURENAME FROM THKQS.Sys_Signature s WHERE s.SYSUSERID= " + userId + " AND s.SIGNATURETYPE=" + type + "");
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetSignatureDataById(decimal id)
        {
            string sql = string.Format(@"SELECT SIGNATUREID,SYSUSERID, SIGNATURETYPE, SIGNATUREPWD, SIGNATUREPATH,STATE,SIGNATURENAME FROM THKQS.Sys_Signature t WHERE t.SIGNATUREID=" + id + "");
            DataTable dt = Query(sql);
            return dt;
        }

        public bool IsSignature(decimal userId, decimal type, string pwd)
        {
            string sql = string.Format(@"SELECT SIGNATUREID,SYSUSERID, SIGNATURETYPE, SIGNATUREPWD, SIGNATUREPATH, STATE,SIGNATURENAME FROM THKQS.Sys_Signature s WHERE s.SYSUSERID=" + userId + @" AND s.SIGNATURETYPE=" + type + " AND s.SIGNATUREPWD='" + pwd + "'");
            DataTable dt = Query(sql);
            bool f = false;
            if (dt != null && dt.Rows.Count > 0)
            {
                f = true;
            }
            else
            {
                f = false;
            }
            return f;
        }

        public DataTable SelectBySign(decimal userId, decimal type)
        {
            string sql = string.Format(@"SELECT
    SIGNATUREID,
    SYSUSERID,
    CASE s.SIGNATURETYPE
    WHEN '1' THEN '私章'
    WHEN '0' THEN '公章'
    END as SIGNATURETYPE,
    SIGNATUREPWD,
    SIGNATUREPATH,
    STATE,
    SIGNATURENAME
FROM
    THKQS.Sys_Signature s
WHERE
    s.SYSUSERID= " + userId + " AND s.SIGNATURETYPE=" + type + "");
            DataTable dt = Query(sql);
            return dt;
        }

        /// <summary>
        /// 根据签章ID查询签章图片路径
        /// </summary>
        /// <param name="signatureID"></param>
        /// <returns></returns>
        public DataTable GetSignatureDataByID(decimal signatureID)
        {
            string sql = string.Format(@"select signatureid,sysuserid, signaturetype, signaturepwd, signaturepath,state,
                 signaturename from SYS_SIGNATURE t where t.signatureid=" + signatureID + "");

            DataTable dt = Query(sql);

            return dt;
        }
    }
}